package com.ztc.ZtcTransportArea.dao;

import com.aqgj.SysArea.bean.SysArea;
import com.aqgj.util.BaseDaoImpl;
import com.ztc.ZtcTransport.bean.ZtcTransport;
import com.ztc.ZtcTransportArea.bean.ZtcTransportArea;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository("ZtcTransportAreaDao")
public class ZtcTransportAreaDao extends BaseDaoImpl<ZtcTransportArea> {

    //获取存在管理区域的省份区域
    public List<SysArea> getExitCompanyOfArea() {
        String sql="select a.* from ztc_transport_area z,sys_area a where z.area_id=a.ID and a.LEVELS=1 and a.PID='000000'";
      return this.currentSession().createNativeQuery(sql,SysArea.class).getResultList();
    }

    //获取存在管理区域的省公司信息
    public List<ZtcTransport> getAreaCompany(String areaId,Integer type) {
        String sql = "SELECT c.* FROM ztc_transport c,ztc_transport_area z WHERE c.id=z.transport_id AND z.area_id='"+areaId+"' and c.`type`="+type;
       return this.currentSession().createNativeQuery(sql,ZtcTransport.class).getResultList();
    }

    //获取存在管理区域的地级
    public List<SysArea> getExitCompanyOfAreaList2(String areaId) {
        String sql="SELECT a.* FROM ztc_transport_area z,sys_area a WHERE z.area_id=a.ID AND ((a.LEVELS=3 AND a.type=1 AND a.pid in (select x.id from sys_area x where x.PID='"+areaId+"')) OR (a.LEVELS=2 AND a.type=2 and a.PID='"+areaId+"'))";
        return this.currentSession().createNativeQuery(sql,SysArea.class).getResultList();
    }

    //首页获取本公司管理区域关系及运营公司数量
    public Map getAreaRelationByCompanyId(String tid,Integer type) {
        String sql="SELECT t.full_name,t.`type`, GROUP_CONCAT(CONCAT_WS('|',if(a.LEVELS=3,if(a.`TYPE`=1,CONCAT_WS('',a.name,'区'),CONCAT_WS('',a.name,'市')),a.NAME), IF(a.LEVELS=3,( " +
                    "SELECT COUNT(*) " +
                    "FROM ztc_transport co " +
                    "WHERE co.area_code =a.CODE AND co.`type`=3 and co.`status` in(3,5,6)), " +
                    "(SELECT COUNT(*) " +
                    "FROM ztc_transport co " +
                    "WHERE co.area_code IN ( " +
                    "SELECT s4.CODE " +
                    "FROM sys_area s " +
                    "INNER JOIN sys_area s3 ON s3.PID=s.id " +
                    "INNER JOIN sys_area s4 ON s4.PID=s3.id " +
                    "WHERE s.CODE=a.CODE) AND co.`type`=3 and co.`status` in(3,5,6))))) manage,a3.NAME province,a1.NAME location " +
                    "FROM ztc_transport_area z " +
                    "INNER JOIN ztc_transport t ON z.transport_id=t.id " +
                    "INNER JOIN sys_area a ON z.area_id=a.ID " +   //公司管理区域
                    "INNER JOIN sys_area a1 ON t.area_code=a1.CODE " +  //公司所在区域
                    "INNER JOIN sys_area a2 ON a2.ID=a1.PID " +
                    "INNER JOIN sys_area a3 ON a3.ID=a2.PID " +    //公司所在省份
                    "WHERE t.id='"+tid+"' GROUP BY t.id ";
        if(type==0){
            sql="select c.full_name,c.`type`,(SELECT GROUP_CONCAT(CONCAT_WS('|',a.NAME, (SELECT COUNT(*) " +
                    "FROM ztc_transport co " +
                    "WHERE co.area_code IN ( " +
                    "SELECT s4.CODE FROM sys_area s " +
                    "INNER JOIN sys_area s3 ON s3.PID=s.id " +
                    "INNER JOIN sys_area s4 ON s4.PID=s3.id " +
                    "WHERE s.CODE=a.CODE) AND co.`type`=3 and co.`status` in(3,5,6)))) manage " +
                    "FROM ztc_transport_area z " +
                    "right JOIN sys_area a ON z.area_id=a.ID " +
                    "WHERE a.LEVELS=1) manage,a3.NAME province,a1.NAME location from ztc_transport c " +
                    "INNER JOIN sys_area a1 ON c.area_code=a1.CODE " +
                    "INNER JOIN sys_area a2 ON a2.ID=a1.PID " +
                    "INNER JOIN sys_area a3 ON a3.ID=a2.PID  where c.`type`=0";
        }
        return (Map) this.currentSession().createNativeQuery(sql).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList().get(0);
    }

}
